<?

//Data loading
$title='REPORT SUMMARY ADVANCE PERFORMANCE';
$curr = GetParam("curr","");
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));


else $perush = 'PT. MASAJI PRAYASA CARGO';

$page   = 'PAGE';
$TableName = "ais_advance";
$DEFINE_FONT=0;
$pdf=new PDFReport("L","mm","Letter");
$pdf->AddPage();
$Result = ReportList ("", "", "");
if ($Result) $pdf->ReportTable($Result);
$pdf->Output();



function ReportList ($Space, $Index, $ParentID) {
            global $RS,$Index,$TableName,$curr;
            $DBConnection   = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
            if (!$Index) $Index = 0;

/*
$SQL = "
            SELECT $TableName.*,tbljoborder.jo_code as jo_noref, sum($TableName.jumlah) as sumjumlah, date_format($TableName.tglbkt,'%d/%m/%y') as tgl, ais_memorial.nobkt as entry3, ais_memorial.jumlah as amount3, IF (L.nobkt<>'',L.nobkt,IF(LZ.nobkt<>'',LZ.nobkt,'')) as entry4, IF (L.jumlah<>'',L.jumlah,IF(LZ.jumlah<>'',LZ.jumlah,'')) as amount4, bb_bk.jumlah as amount2, bb_bk.nobkt as aslref
            FROM `$TableName`
            INNER JOIN
            ais_memorial bb_bk ON $TableName.nobkt=bb_bk.noref AND bb_bk.ktrasl=$TableName.ktrasl
            AND bb_bk.urut=999
            LEFT JOIN
            ais_memorial ON bb_bk.nobkt=ais_memorial.noref AND ais_memorial.ktrasl=$TableName.ktrasl AND ais_memorial.skada=$TableName.skada AND ais_memorial.nobkt like 'M%'
            LEFT JOIN
            ais_memorial L ON L.noref=bb_bk.nobkt AND L.ktrasl=bb_bk.ktrasl AND L.skada=bb_bk.skada AND L.nobkt not like 'M%'
            LEFT JOIN
            ais_memorial_z LZ ON LZ.noref=bb_bk.nobkt AND LZ.ktrasl=bb_bk.ktrasl AND LZ.urut=999  AND LZ.nobkt not like 'M%'
            LEFT JOIN tbljoborder on ais_advance.jo_id = tbljoborder.jo_id
          ";
*/          

$SQL = "
            SELECT $TableName.*,tbljoborder.jo_code as jo_noref, sum($TableName.jumlah) as sumjumlah, date_format($TableName.tglbkt,'%d/%m/%y') as tgl, ais_memorial.nobkt as entry3, ais_memorial.jumlah as amount3, IF (L.nobkt<>'',L.nobkt,IF(LZ.nobkt<>'',LZ.nobkt,'')) as entry4, IF (L.jumlah<>'',L.jumlah,IF(LZ.jumlah<>'',LZ.jumlah,'')) as amount4, bb_bk.jumlah as amount2, bb_bk.nobkt as aslref
            FROM `$TableName`
            INNER JOIN
            ais_memorial bb_bk ON $TableName.nobkt=bb_bk.noref AND bb_bk.ktrasl=$TableName.ktrasl
            AND bb_bk.urut=999
            LEFT JOIN
            ais_memorial ON bb_bk.nobkt=ais_memorial.noref AND ais_memorial.ktrasl=$TableName.ktrasl AND ais_memorial.nobkt like 'M%'
            LEFT JOIN
            ais_memorial L ON L.noref=bb_bk.nobkt AND L.ktrasl=bb_bk.ktrasl AND L.nobkt not like 'M%'
            LEFT JOIN
            ais_memorial_z LZ ON LZ.noref=bb_bk.nobkt AND LZ.ktrasl=bb_bk.ktrasl AND LZ.urut=999  AND LZ.nobkt not like 'M%'
            LEFT JOIN tbljoborder on ais_advance.jo_id = tbljoborder.jo_id
          ";         
          

    if (GetParam("fromDate",""))
    {
        list($day1,$month1,$year1) = explode("/",GetParam("fromDate",""));
        $FDate = "$year1-$month1-$day1";
    }

    if (GetParam("toDate",""))
    {
        list($day2,$month2,$year2) = explode("/",GetParam("toDate",""));
        $TDate = "$year2-$month2-$day2";
    }


    if ($FDate)
        if (strlen($WHERE)) $WHERE .= " AND ".$TableName.".tglbkt >= '".$FDate."' ";
        else $WHERE .= $TableName.".tglbkt >= '".$FDate."' ";

    if ($TDate)
        if (strlen($WHERE)) $WHERE .= " AND ".$TableName.".tglbkt <= '".$TDate."' ";
        else $WHERE .= $TableName.".tglbkt <= '".$TDate."' ";

    if (GetParam("office",""))
        if (strlen($WHERE)) $WHERE .= " AND ".$TableName.".ktrasl = '".GetParam("office","")."' ";
        else $WHERE .= $TableName.".ktrasl = '".GetParam("office","")."' ";


    if (strlen($WHERE)) $WHERE = " WHERE (ais_memorial.nobkt like 'MM%' OR ais_memorial.nobkt like 'MB%') AND ".$TableName.".curr like '%".$curr."%' AND ".$WHERE;
        else $WHERE = " WHERE (ais_memorial.nobkt like 'MM%' OR ais_memorial.nobkt like 'MB%') AND ".$TableName.".curr like '%".$curr."%' ";


    $SQL = $SQL.$WHERE;



$SQL .= " GROUP BY $TableName.nobkt";
$SQL .= " ORDER BY $TableName.tglbkt DESC ";

//echo $SQL;

                           //die($SQL);
                          $MyResult = $DBConnection->dbc->get_results($SQL,ARRAY_A);
                          return $MyResult;
}

function ReportList2 ($Space, $Index, $ParentID) {
            global $RS,$Index,$TableName;
            $DBConnection   = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
            if (!$Index) $Index = 0;

$SQL = "
            SELECT $TableName.*, sum($TableName.jumlah) as sumjumlah, date_format($TableName.tglbkt,'%d/%m/%y') as tgl, ais_memorial.nobkt as entry3, ais_memorial.jumlah as amount3, IF (L.nobkt<>'',L.nobkt,IF(LZ.nobkt<>'',LZ.nobkt,'')) as entry4, IF (L.jumlah<>'',L.jumlah,IF(LZ.jumlah<>'',LZ.jumlah,'')) as amount4, bb_bk.jumlah as amount2, bb_bk.nobkt as aslref
            FROM `$TableName`
            INNER JOIN
            ais_memorial bb_bk ON $TableName.nobkt=bb_bk.noref AND bb_bk.ktrasl=$TableName.ktrasl
        AND bb_bk.urut=999
            LEFT JOIN
            ais_memorial ON bb_bk.nobkt=ais_memorial.noref AND ais_memorial.ktrasl=$TableName.ktrasl AND ais_memorial.skada=$TableName.skada AND ais_memorial.nobkt like 'M%'
            LEFT JOIN
            ais_memorial L ON L.noref=bb_bk.nobkt AND L.ktrasl=bb_bk.ktrasl AND L.skada=bb_bk.skada AND L.nobkt not like 'M%'
            LEFT JOIN
            ais_memorial_z LZ ON LZ.noref=bb_bk.nobkt AND LZ.ktrasl=bb_bk.ktrasl AND LZ.urut=999  AND LZ.nobkt not like 'M%'
          ";

                        if (GetParam("office",""))
                          $SQL   .= "AND $TableName.ktrasl='".GetParam("office","")."' ";
//                          $WHERE  .=  " AND $TableName.aslref<>''";
//                          $WHERE  .=  " AND ifnull(bb_bk.jumlah,0)-ifnull(ais_memorial.jumlah,0) <> 0 AND IF (L.nobkt<>'',L.nobkt,IF(LZ.nobkt<>'',LZ.nobkt,''))='' ";
                       list($day,$month,$year) = explode("/",GetParam("fromDate",""));
                       $FDate = "$year-$month-$day";
                       list($day,$month,$year) = explode("/",GetParam("toDate",""));
                       $TDate = "$year-$month-$day";
                       $SQL .= " AND $TableName.tglbkt >= '$FDate' AND $TableName.tglbkt<='$TDate' ";

                          $SQL .= " GROUP BY $TableName.nobkt";
                            $SQL .= " ORDER BY $TableName.tglbkt DESC ";

                           //die($SQL);
                          $MyResult = $DBConnection->dbc->get_results($SQL,ARRAY_A);
                          return $MyResult;
}

class PDFReport extends PDF {

    function Header() {
        global $title,$perush,$page,$DEFINE_FONT;
        if ($DEFINE_FONT==0) {
            $this->AddFont('arialn','','arialn.php');
            $DEFINE_FONT=1;
        }
        $this->SetFont('arialn','',12);
                $this->Cell(195,5,$perush,0,1,'C',0);
                $this->Cell(195,5,$title,0,1,'C',0);
                $SystemDate = date("d/m/Y");
                $this->Cell(195,5,"REPORT DATE: ".$SystemDate,0,1,'C',0);
                $this->Cell(195,5,$page." ".$this->PageNo(),0,1,'R',0);
                $this->Line(10,30,205,30);
                $this->Ln();
                $this->Ln();
                //Header

                //Column widths
               $w=array(22,12,35,22,22,20,22,20,22,20);
                $this->SetFont('arialn','',8);

                $this->Cell($w[0],7,"TRANS NO",1,0,'C',0);
                $this->Cell($w[1],7,"DATE",1,0,'C',0);
                $this->Cell($w[2],7,"PAY TO",1,0,'C',0);
                $this->Cell($w[3],7,"REFERENCE",1,0,'C',0);
                $this->Cell($w[4],7,"BK/BB",1,0,'C',0);
                $this->Cell($w[5],7,"AMOUNT",1,0,'C',0);
                $this->Cell($w[6],7,"MEMORIAL",1,0,'C',0);
                $this->Cell($w[7],7,"AMOUNT",1,0,'C',0);
                $this->Cell($w[8],7,"CASH/BANK",1,0,'C',0);
                $this->Cell($w[9],7,"AMOUNT",1,0,'C',0);

                $this->Ln();
    }

    function ReportTable($Result) {
        //Column widths
        $w=array(22,12,35,22,22,20,22,20,22,20);
        //Data
        foreach($Result as $row)
        {

                $no++;
                $this->Cell($w[0],6,$row["nobkt"],'LR');
                $this->Cell($w[1],6,$row["tgl"],'LR');

                if (strlen($row["debnam"])>20)
                $row["debnam"] = substr($row["debnam"], 0, 20);

                $this->Cell($w[2],6,$row["debnam"],'LR');
                $this->Cell($w[3],6,$row["jo_noref"],'LR');
                $this->Cell($w[4],6,$row["aslref"],'LR',0,'R');
                $this->Cell($w[5],6,number_format($row["amount2"],2),'LR',0,'R');

                $TotalAmount2 = $row["amount2"] + $TotalAmount2;
                if ($row["entry3"]=='')
                $TotalOutstanding = $row["amount2"] + $TotalOutstanding;

                $this->Cell($w[6],6,$row["entry3"],'LR',0,'R');
                $this->Cell($w[7],6,number_format($row["amount3"],2),'LR',0,'R');

                $TotalAmount3 = $row["amount3"] + $TotalAmount3;
                $this->Cell($w[8],6,$row["entry4"],'LR',0,'R');
                $this->Cell($w[9],6,number_format($row["amount4"],2),'LR',0,'R');

                $TotalAmount4 = $row["amount4"] + $TotalAmount4;

            $this->Ln();
        }


            $this->Cell($w[0]+$w[1]+$w[2]+$w[3]+$w[4],6,"TOTAL:",'LTR');
                $this->Cell($w[5],6,number_format($TotalAmount2,2),'LTR',0,'R');

                $this->Cell($w[6],6,"",'LTR',0,'R');
                $this->Cell($w[7],6,number_format($TotalAmount3,2),'LTR',0,'R');

                $this->Cell($w[8],6,"",'LTR',0,'R');
                $this->Cell($w[9],6,number_format($TotalAmount4,2),'LTR',0,'R');
            $this->Ln();

            $this->Cell($w[0]+$w[1]+$w[2]+$w[3]+$w[4],6,"TOTAL PERFORMANCE:",'LTR');
                $this->Cell($w[5],6,number_format($TotalAmount2,2),'LTR',0,'R');

                $this->Cell($w[6],6,"",'T',0,'R');
                $this->Cell($w[7],6,"",'T',0,'R');

                $this->Cell($w[8],6,"",'T',0,'R');
                $this->Cell($w[9],6,"",'TR',0,'R');
            $this->Ln();

        //Closure line
        //$this->Cell(array_sum($w),0,'','T');
    }

    function Footer() {
        //Closure line
        $this->Cell(217,0,'','T');
    }

}


?>
